/*--------------------------------
一、访问控制和子程序编程

    *********本节中的所有的操作需要管理员权限；*********
    
1、创建用户、角色
   创建一个用户，并给该用户赋予不同的权限；
   (1) 创建用户
       语法：
            create user 用户名 identified by 密码;
   (2) 给用户赋予权限
       语法：
            grant 权限列表|角色列表 to 用户列表|角色列表|public with 语句
   
   创建角色
       角色是就是一组权限的别名；
       不同的角色对应不用的权限列表；
       可以通过对角色的管理，进而对权限进行管理；               
   语法：
       create role 角色名;
   角色创建完成之后，和用户一样，使用grant语句进行赋予权限；
   这个角色也可以使用 grant 赋给其他用户；
       
   撤销权限：
       系统管理员可以某个用于的某些权限；
       语法：
            revoke 权限列表|角色列表  from  用户列表|角色列表|public  with grant option
            
       解析：
           with语句的作用是级联撤销；A赋予权限给用B，B赋予权限给C，如果级联撤销B的权限，则C也会被撤销；
                
二、子程序编程
    Oracle中的子程序编程主要有pl编程、存储过程、函数、触发器；
    有关pl编程内容，课下扩展ppt中的内容；
1、存储过程
   存储过程是Oracle中对于可以执行的pl脚本的封装；是Oracle中可以执行的代码语句块。
   
   扩展作业：
       存储过程和函数的异同点；参见教材P148;
   
   语法：
        create or replace procedure schema.pname
              [(参数定义类表)]
              is|as
              begin
                 存储过程体;
              
              end pname;        
   解析：
       （1）存储过程名 pname 的命名和序列的命名规范一样；例如：前缀_存储过程名[_后缀]；
       （2）参数定义列表语法：
                参数名  in|out|in out 参数类型
            其中，in是默认的表示这是一个传入参数；外部向存储过程传值；
                  out是传出参数，从存储过程内容向外部传值；
                  in out 既是传入参数也是传出参数；   

   删除存储过程：
       语法：
           drop procedure 存储过程名;

2、函数
   oracle可以通过自定函数来完成业务逻辑处理；类似于 nvl、to_char、to_date函数
   
   语法：
       create or replace  function 函数名
            [(参数定义类表)]
            return 数据类型
              is|as
                 局部参数列表
              begin
                 存储过程体;
              
              end 函数名; 
   解析：
       （1）函数处理有返回值之外，也可以使用out参数继续返回值；【返回多个值】    
       
       
   删除函数：
       语法：
           drop function 函数名;
           
3、游标
   游标是对查询结果的处理；
   游标是sql查询结果集的缓存机制【缓存在内存中的一张虚拟表】；
   通过对这张缓存表（游标）的操作，完成对sql语句结果集的操作；
   游标是访问sql语句执行结果的一种途径；
   游标分为：
       隐式游标：
           select into，以及dml操作会自动创建隐式游标；
       显式游标：        
           select结果集中有多行多列结果，则需要创建显式游标来缓存结果集，并通过游标对其进行处理；
    游标四个属性：
        %rowcount -- 游标中的行数
        %found -- 布尔，是否还有数据；类似于java迭代器中hasNext
        %notfound -- 布尔，游标是否已经没有数据
        %isopen -- 布尔，游标是否打开        
    
    游标编程的流程：
        （1）声明一个游标
             cursor 游标名 [参数列表] is  select 查询;
        （2）打开游标
             open 游标名[参数列表];
        （3）提取数据（业务逻辑处理）
             fetch 游标名 into [参数列表];
             注意： into后面的参数列表要和 游标声明时的参数列表保持一致【个数、数据类型兼容】;
        （4）关闭游标       
              close 游标名;

4、触发器
   触发器也是Oracle的对象，是在一定的时机下而触发一系列动作；
   触发器包含触发时机和触发引起的动作行为；
   触发器包含以下几部分：
       触发时间     before    after
       出发事件     insert、update、delete   【触发时间和触发事件进行组合】
       触发类型     statement【语句触发器】、row【行触发器】
       触发器动作     触发器执行内容  
    注意：
        语句触发器触发之后只执行一次；
        行触发器触发之后有多行执行多少次【每行执行一次】
    
    行触发器中有Old和new伪对象的使用：
        old -- 表示触发事件发生之前的那一条数据；
        new -- 表示触发时间发生之后的那一条数据；
    
    根据触发事件，old和new的意义如下：
        
        insert ：只有new 没有old ，new表示被插入的行数据；
        update ：有new和old，修改之前的数据行是old；修改之后的数据行是new；
        delete ：只有old没有new；删除之前的数据行是old；
        
        insert、update、delete三种事件都可以和 before、after搭配。
        
        
                   
--------------------------------*/

--- 创建一个用户，并赋予基本的应用角色权限 resource、connect 
-- 创建一个用户
create user yuw2018 identified by oracle;
-- 给用户赋予权限
grant resource,connect to yuw2018;

--- 存储过程应用
-- 创建一个存储过程操作的表
create table emps30 as select * from employees; 
-- 查看数据
select * from emps30 where department_id = 60;
select * from employees where department_id = 60;
--- 根据不同部门id对该部门的员工进行加薪处理（加薪百分比小数作为参数）

--- 创建一个存储过程
create or replace procedure pro_rainseSalaryBydetpId
-- 参数列表
       (deptId in emps30.department_id%type,salaryRate in number)
       is
       begin
           --- 进行加薪处理
           update emps30 set salary = salary * (1+nvl(salaryRate,0))
                  where department_id = deptId;
           commit;
       end pro_rainseSalaryBydetpId;

--- 存储过程有错误，删掉重新创建
drop procedure pro_rainseSalaryBydetpId;

--- 执行存储过程
begin   
pro_rainseSalaryBydetpId(60,0.2);
end;

---- 使用out参数返回值
--- 根据员工编号empid对该员工进行加薪处理（加薪百分比小数作为参数）

--- 创建一个存储过程
create or replace procedure pro_rainseSalaryByEmpId
-- 参数列表
       (empId in emps30.employee_id%type,salaryRate in number,newSalary out emps30.salary%type)
       is
       begin
           --- 进行加薪处理
           update emps30 set salary = salary * (1+nvl(salaryRate,0))
                  where employee_id = empId;
           --- 将修改之后的工资值赋值给out参数
           select salary into newSalary from emps30 where employee_id = empId;     
           commit;
       end pro_rainseSalaryByEmpId;

--- 调用存储过程

-- 定义一个变量
-- pl编程用法
-- 声明一个变量，类似java生命了一个局部变量
declare   
     newSalary  NUMBER;
     empid number:=&empid;
     salaryRate number :=&salaryRate;
begin
-- 调用存储过程 -- 变量 newSalary 作为out参数传值给存储过程
pro_rainseSalaryByEmpId(empid,salaryRate,newSalary);
-- 查询变量 -- oracle控制台输出函数 类似于java的 System.out.print
DBMS_OUTPUT.PUT_LINE(newSalary);
end;

---------------- 函数
--- 根据员工编号empid对该员工进行加薪处理（加薪百分比小数作为参数）
--- 创建函数
create or replace function fun_rainseSalaryByEmpId
       --- 函数形参列表
       (empId in emps30.employee_id%type,salaryRate in number)
       --- 返回值类型  
       return number;
       is
       --- 局部参数定义
       -- 返回变量，默认工资
       newSalary emps30.salary%type;
       begin
          -- 修改员工工资
          --- 进行加薪处理
           update emps30 set salary = salary * (1+nvl(salaryRate,0))
                  where employee_id = empId;
           --- 将修改之后的工资值赋值给out参数
           select salary into newSalary from emps30 where employee_id = empId;     
           commit;
           return newSalary;
       end fun_rainseSalaryByEmpId;

-- 自定义函数的执行和内置函数一样；
-- 但是select查询中不能执行update操作的函数；

---------------游标编程
-- 1 声明游标
declare
   -- 定义游标
   cursor cursor_emp is select first_name,salary from employees;
   -- 定义变量
   empName employees.first_name%type;
   empSalary employees.salary%type;
begin
-- 2 开发游标
   open cursor_emp;
-- 3 取出数据（使用loop循环）
   loop
     -- 取出数据
     fetch cursor_emp into empName,empSalary;
     -- 循环结束条件
     exit when cursor_emp%notfound;
     -- 想控制台输出内容
     dbms_output.put_line(empName||'员工的工资为：'||empSalary);   
   end loop;
end;


---------------使用序列和触发器实现主键字段的自增操作-----------------------
-- 创建一个测试的表
create table emps40(emp_id,emp_name,emp_salary) 
       as select e.employee_id,e.first_name,e.salary from employees e where 1=2; 
-- 添加主键约束
alter table emps40 
   add constraint emps40_empId_pk primary key(emp_id);

-- 1、创建一个该表主键使用的序列对象
create sequence seq_emps40_empId 
      nomaxvalue
      nocycle
      nocache;
      
-- 2、创建一个触发器，在新增时把序列的值赋值给主键字段
create trigger trigger_emps40_empId
      before insert
      on emps40
      for each row
      begin
          --- 将序列的值赋值给要出入的数据记录的主键字段
          -- 注意：使用为对象 new或old时，需要使用 :new  :old
          select seq_emps40_empId.nextval into :new.emp_id from dual;      
      end;  

-- 测试自增主键
insert into emps40(emp_name,emp_salary) values('李四',2500);

-- 查看数据
select * from emps40;
